package com.example.coder_voucher.repository;

import com.example.coder_voucher.entity.Voucher;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface VoucherRepository extends JpaRepository<Voucher, Long> {
    
    /**
     * Find voucher by code
     */
    Optional<Voucher> findByCode(String code);
    
    /**
     * Find all active vouchers that are available for claiming
     */
    @Query("SELECT v FROM Voucher v WHERE v.status = 'ACTIVE' " +
           "AND v.startTime <= :now AND v.endTime > :now " +
           "AND v.claimedQuantity < v.totalQuantity")
    List<Voucher> findAvailableVouchers(@Param("now") LocalDateTime now);
    
    /**
     * Find vouchers by status
     */
    List<Voucher> findByStatus(Voucher.VoucherStatus status);
    
    /**
     * Find vouchers that are expired but status is still ACTIVE
     */
    @Query("SELECT v FROM Voucher v WHERE v.status = 'ACTIVE' AND v.endTime < :now")
    List<Voucher> findExpiredActiveVouchers(@Param("now") LocalDateTime now);
    
    /**
     * Check if voucher code already exists
     */
    boolean existsByCode(String code);
    
    /**
     * Find vouchers by name containing keyword (case insensitive)
     */
    List<Voucher> findByNameContainingIgnoreCase(String keyword);
    
    /**
     * Find vouchers created between dates
     */
    List<Voucher> findByCreatedAtBetween(LocalDateTime startDate, LocalDateTime endDate);
}